SQLのWHERE句でフィルタリングする時はNULLに気をつけようねという話

hanzawa.yuya

2024.06.28

Google Cloudデータエンジニアのはんざわです。
今回はSQLのWHERE句でのNULLの扱いについて、具体的な例とその原因を交えて紹介したいと思います。

また、本ブログでは使い慣れているBigQueryとネイティブにサポートしているGoogleSQLを使用しますが、他のデータベースやSQLでも同様の挙動をするので、ぜひ参考にしてみてください。

結論

WHERE句で特定の値でフィルタリングする際にNULL値を適切に処理しないと意図しない欠損が発生する恐れがあります。
特にDWHの場合、意図しない欠損は、データ分析やレポートの精度に影響を与える可能性がありますので適切な処理を検討してください。

意図しない欠損

例として、次のクエリでは、idが1以外のレコードを取得しようとしていますが、NULLも弾かれてしまいます。

WITH sample_table AS (
  SELECT 
    _array AS id
  FROM
    UNNEST([0, 1, null, 3, 4]) AS _array
)

SELECT
  id 
FROM
  sample_table
WHERE
  id != 1

/* 結果 */
/* NULLも弾かれてしまう */
+----+
| id |
+----+
| 0  |
| 3  |
| 4  |
+----+

対策方法１

一つ目の対処方法として、COALESCEやIFNULLでNULLを明示的に他の値に置き換える方法です。

WITH sample_table AS (
  SELECT 
    _array AS id
  FROM
    UNNEST([0, 1, null, 3, 4]) AS _array
)

SELECT
  id 
FROM
  sample_table
WHERE
  /* NULLを別の値で置換する */
  COALESCE(id, -999999) != 1

/* 結果 */
+------+
|  id  |
+------+
|  0   |
| NULL |
|  3   |
|  4   |
+------+

対策方法２

二つ目の対処法として、別途NULL値を取得するように条件を調整する方法です。

WITH sample_table AS (
  SELECT 
    _array AS id
  FROM
    UNNEST([0, 1, null, 3, 4]) AS _array
)

SELECT
  id 
FROM
  sample_table
WHERE
  id != 1
  /* NULLを取得する */
  OR id IS NULL

/* 結果 */
+------+
|  id  |
+------+
|  0   |
| NULL |
|  3   |
|  4   |
+------+

原因

ほとんどのプログラミング言語では、2値論理が採用されており、TRUEまたはFALSEの2つのみを取ります。
Pythonも例外ではなく、2値論理が採用されています。

Pythonの例

l = [0, 1, None, 3, 4]

for i in l:
  print(bool(i))

> False
True
False
True
True

これに対し、GoogleSQLを始めとするデータベース言語では3値論理が採用されており、次の例のようにTRUE、FALSE、およびnull（UNKNOWN）の3つの値を取ることができます。

SELECT 
  CAST(_array AS BOOL) AS BOOLEAN
FROM
  UNNEST([0, 1, null, 3, 4]) AS _array

/* 結果 */
+----------+
| BOOLEAN  |
+----------+
|  false   |
|  true    |
|  null    |
|  true    |
|  true    |
+----------+

さらにSQLのWHERE句は、条件の評価結果がTRUEである行のみを取得します。
冒頭の例だと、WHERE句でid != 1の条件で評価していますが、その際の評価結果は以下の通りです。

/* 元のデータ */
[0, 1, null, 3, 4]

/* 評価結果 */
[true, false, null, true, true]

/* trueのみを取得 */
[0, 3, 4]

　このように必要に応じて、NULL値を適切に処理する必要があります。

まとめ

本ブログで紹介したようにSQLのWHERE句でNULL値を適切に処理しないと、意図しないデータの欠損が発生する可能性があります。
対策としては、COALESCEやIFNULLを使用してNULLを他の値に置き換える方法や、NULL値を別途取得する条件を追加する方法があります。
SQLの3値論理を理解し、適切に対応することで意図しない結果を防ぐことができます。
ぜひ、今回紹介した方法を活用して、正確なデータ抽出を行ってください。